/*
 * Created on Jun 8, 2004
 * by Mihai Postelnicu
 * email mihai@code.ro
 *
 *
 */
package ro.gateway.aida.search;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import ro.gateway.aida.db.DBPersistenceManager;
import ro.gateway.aida.db.PersistenceToken;

/**
 * @author Mihai Postelnicu email mihai@code.ro
 */
public class ResultsFormatDB extends DBPersistenceManager {

	private ResultsFormatDB(PersistenceToken token) {
		super(token);
	}

	public static ResultsFormatDB getManager(PersistenceToken token) {
		return new ResultsFormatDB(token);
	}

	public void insert(ResultsFormat f) throws SQLException {
		Connection con = getConnection();

		try {
			PreparedStatement ps = con.prepareStatement(INSERT);
			ps.setString(1, f.getName());
			ps.setString(2, f.getFormat());
			ps.setLong(3, f.getUserId());
			ps.executeUpdate();
		} finally {
			con.close();
		}
	}

	public ResultsFormat get(long id) throws SQLException {
		Connection con = getConnection();

		try {
			PreparedStatement ps;

			ps = con.prepareStatement(GET);
			ps.setLong(1, id);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				ResultsFormat f = new ResultsFormat();
				f.setId(id);
				f.setName(rs.getString(1));
				f.setFormat(rs.getString(2));
				f.setPrimary(str2Bool(rs.getString(3)));
				f.setUserId(rs.getLong(4));
				return f;
			}
			return getPrimary();

		} finally {
			con.close();
		}
	}

	public ResultsFormat getPrimary() throws SQLException {
		Connection con = getConnection();

		try {
			PreparedStatement ps;

			ps = con.prepareStatement(GET_PRIMARY);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				ResultsFormat f = new ResultsFormat();
				f.setId(rs.getLong(1));
				f.setName(rs.getString(2));
				f.setFormat(rs.getString(3));
				f.setPrimary(str2Bool(rs.getString(4)));
				return f;
			}
			return null;

		} finally {
			con.close();
		}
	}

	public List getForUser(long user_id, boolean addprimary) throws SQLException {
		Connection con = getConnection();
		ArrayList ret = new ArrayList();

		try {
			if (user_id != -1) {
				PreparedStatement ps;

				ps = con.prepareStatement(GET_LIST);
				ps.setLong(1, user_id);
				ResultSet rs = ps.executeQuery();
				while (rs.next()) {
					ResultsFormat f = new ResultsFormat();
					f.setId(rs.getLong(1));
					f.setName(rs.getString(2));
					f.setFormat(rs.getString(3));
					f.setPrimary(str2Bool(rs.getString(4)));
					ret.add(f);
				}
			}
			if(addprimary) ret.add(getPrimary());
			return ret;

		} finally {
			con.close();
		}
	}

	public void update(ResultsFormat f) throws SQLException {
		Connection con = getConnection();

		try {
			PreparedStatement ps;
			if (f.isPrimary()) {
				ps = con.prepareStatement(CLEAN_PRIMARY);
				ps.executeUpdate();
			}
			ps = con.prepareStatement(UPDATE);
			ps.setLong(4, f.getId());
			ps.setString(1, f.getName());
			ps.setString(2, f.getFormat());
			ps.setString(3, bool2Str(f.isPrimary()));
			ps.executeUpdate();
		} finally {
			con.close();
		}
	}

	public void delete(long id) throws SQLException {
		Connection con = getConnection();

		try {
			PreparedStatement ps = con.prepareStatement(DELETE);
			ps.setLong(1, id);
			ps.executeUpdate();
		} finally {
			con.close();
		}
	}

	public static final String GET_LIST = "SELECT id,name,format,prim FROM result_formats WHERE user_id=?";
	public static final String GET_PRIMARY = "SELECT id,name,format,prim FROM result_formats WHERE prim='Y'";
	public static final String GET = "SELECT name,format,prim,user_id FROM result_formats WHERE id=?";
	public static final String INSERT = "INSERT INTO result_formats (name,format,user_id) VALUES (?,?,?)";
	public static final String UPDATE = "UPDATE result_formats SET name=?, format=?, prim=? WHERE id=?";
	public static final String CLEAN_PRIMARY = "UPDATE result_formats SET prim='N'";
	public static final String DELETE = "DELETE FROM result_formats WHERE id=?";
}